package com.lm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.lm.dao.TaoCanDAO;
import com.lm.dto.TaoCanDTO;
import com.lm.model.Subtaocan;
import com.lm.model.Taocan;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.util.DBUtil;

public class TaoCanDAOImpl implements TaoCanDAO {
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public List getAllTaoCanListByEventID(String eventID) throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_taocan ");
		sb.append(" where eventID=").append(Integer.parseInt(eventID));

		con = dataSource.getConnection();
		pStmt = con.prepareStatement(sb.toString());
		rs = pStmt.executeQuery();
		try {
			while (rs.next()) {
				Taocan tc = new Taocan();

				tc.setId(rs.getInt("id"));
				tc.setCardMinux(rs.getDouble("cardMinux"));
				tc.setEventId(rs.getInt("eventID"));
				tc.setTaoCanName(rs.getString("TaoCanName"));
				tc.setTotalCardBid(rs.getDouble("totalCardBid"));
				tc.setTotalMarketBid(rs.getDouble("totalMarketBid"));

				resList.add(tc);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;

	}

	public List getAllSubTaoCanListByTaoCanID(String taocanID) throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select lms.id,lp.productNo,lb.brandName_cn,lp.productNo,");
		sb.append(" lms.marketBid,lms.cardBid,lms.taocanID");
		sb.append(" from lm_subtaocan lms,lm_product lp,lm_brand lb");
		sb.append(" where taocanID=").append(Integer.parseInt(taocanID));
		sb.append(" and lp.id=lms.productID");
		sb.append(" and lb.id=lms.brandID");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				TaoCanDTO lmDTO = new TaoCanDTO();
				lmDTO.setId(String.valueOf(rs.getInt("lms.id")));
				lmDTO.setProductNO(rs.getString("lp.productNo"));
				lmDTO.setBrandName_cn(rs.getString("lb.brandName_cn"));
				lmDTO.setXinghao(rs.getString("lp.productNo"));
				lmDTO.setMarketbid(String
						.valueOf(rs.getDouble("lms.marketBid")));
				lmDTO.setCardbid(String.valueOf(rs.getDouble("lms.cardBid")));
				lmDTO.setTaocanID(String.valueOf(rs.getInt("lms.taocanID")));

				resList.add(lmDTO);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public TaoCanDTO getSubTaoCanByTaoCanIDAndSubTaoCanID(String taocanID,
			String subTaoCanID) throws Throwable {
		TaoCanDTO lmDTO = new TaoCanDTO();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select lms.id,lb.brandName_cn,lp.productNo,");
		sb.append(" lms.marketBid,lms.cardBid,lms.taocanID,lt.typeName");
		sb
				.append(" from lm_subtaocan lms,lm_product lp,lm_brand lb,lm_type lt");
		sb.append(" where taocanID=").append(Integer.parseInt(taocanID));
		sb.append(" and lms.id=").append(Integer.parseInt(subTaoCanID));
		sb.append(" and lp.id=lms.productID");
		sb.append(" and lb.id=lms.brandID");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				lmDTO.setId(String.valueOf(rs.getInt("lms.id")));
				// lmDTO.setProductName(rs.getString("lp.productName"));
				lmDTO.setBrandName_cn(rs.getString("lb.brandName_cn"));
				lmDTO.setXinghao(rs.getString("lp.productNo"));
				lmDTO.setMarketbid(String
						.valueOf(rs.getDouble("lms.marketBid")));
				lmDTO.setCardbid(String.valueOf(rs.getDouble("lms.cardBid")));
				lmDTO.setTaocanID(String.valueOf(rs.getInt("lms.taocanID")));
				lmDTO.setTypeName(rs.getString("lt.typeName"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return lmDTO;
	}

	public double getTotalMarketBid(String taocanID) throws Throwable {
		double total = 0.0;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select sum(marketBid) as total");
		sb.append(" from lm_subtaocan ");
		sb.append(" where taocanID=").append(Integer.parseInt(taocanID));
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				total = rs.getDouble("total");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return total;
	}

	public double getTotalCardBid(String taocanID) throws Throwable {
		double total = 0.0;

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append(" select sum(cardBid) as total");
		sb.append(" from lm_subtaocan ");
		sb.append(" where taocanID=").append(Integer.parseInt(taocanID));
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				total = rs.getDouble("total");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return total;
	}

	public void addTaoCan(Taocan tc) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append(" insert into lm_taocan(TaoCanName,eventID)");

			sb.append(" values('").append(tc.getTaoCanName()).append("',");
			sb.append(tc.getEventId()).append(")");

			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void addSubTaoCan(Subtaocan stc, String taocanID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb
					.append(" insert into lm_subtaocan(productID,brandID,typeID,marketBid,cardBid,taocanID)");
			sb.append(" values(").append(stc.getProductId()).append(",");
			sb.append(stc.getBrandId()).append(",");
			sb.append(stc.getTypeId()).append(",");
			sb.append(stc.getMarketBid()).append(",");
			sb.append(stc.getCardBid()).append(",");
			sb.append(stc.getTaocanId()).append(")");

			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public Taocan getTaoCanByIDAndEventID(String taocanID, String eventID)
			throws Throwable {
		Taocan tc = new Taocan();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append(" select * from lm_taocan  ");
			sb.append(" where eventID= ").append(Integer.parseInt(eventID));
			sb.append(" and id=").append(Integer.parseInt(taocanID));

			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				tc.setTaoCanName(rs.getString("TaoCanName"));
				tc.setEventId(rs.getInt("eventID"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return tc;
	}

	public Taocan getTaoCanByID(String taocanID) throws Throwable {
		Taocan tc = new Taocan();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append(" select * from lm_taocan  ");
			sb.append(" where id=").append(Integer.parseInt(taocanID));

			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				tc.setTaoCanName(rs.getString("TaoCanName"));
				tc.setEventId(rs.getInt("eventID"));
				tc.setCardMinux(rs.getDouble("cardMinux"));
				tc.setTotalCardBid(rs.getDouble("totalCardBid"));
				tc.setTotalMarketBid(rs.getDouble("totalMarketBid"));
				tc.setId(rs.getInt("id"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return tc;
	}

	public void editTaoCanByTaoCanID(Taocan tc, String taocanID)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append("update lm_taocan set TaoCanName='").append(
				tc.getTaoCanName()).append("'");
		sb.append(" where id=").append(Integer.parseInt(taocanID));
		sb.append(" and eventID=").append(tc.getEventId());
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void editTaoCanByTaoCanID(double totalCardBid,
			double totalMarketBid, double CardMinus, String taocanID)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append("update lm_taocan set totalMarketBid=")
				.append(totalMarketBid);
		sb.append(",totalCardBid=").append(totalCardBid);
		sb.append(",cardMinux=").append(CardMinus);
		sb.append(" where id=").append(Integer.parseInt(taocanID));
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void editSubTaoCanBySubTaoCanID(Subtaocan stc,
			String subtaocanID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();

		sb.append("update lm_subtaocan set marketBid=").append(
				stc.getMarketBid());
		sb.append(",cardBid=").append(stc.getCardBid());
		sb.append(" where id=").append(Integer.parseInt(subtaocanID));
		sb.append(" and taocanID=").append(stc.getTaocanId());
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void deleteTaoCanByEventIDAndTaoCanID(String taocanID, String eventID)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append(" delete from lm_taocan ");
		sb.append(" where id=").append(Integer.parseInt(taocanID));
		sb.append(" and eventID=").append(Integer.parseInt(eventID));

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void deleteTaoCanByEventID(String eventID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append(" delete from lm_taocan ");
		sb.append(" where eventID=").append(Integer.parseInt(eventID));

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void deleteSubTaoCanByTaoCanIDAndSubTaoCanID(String taocanID,
			String subtaocanID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append(" delete from lm_subtaocan ");
		sb.append(" where id=").append(Integer.parseInt(subtaocanID));
		sb.append(" and taocanID=").append(Integer.parseInt(taocanID));
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void deleteSubTaoCanByTaoCanIDs(String taocanIDs) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append(" delete from lm_subtaocan ");
		sb.append(" where  taocanID in (").append(taocanIDs).append(")");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void deleteSubTaoCanByTaoCanID(String taocanID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append(" delete from lm_subtaocan ");
		sb.append(" where taocanID=").append(Integer.parseInt(taocanID));
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}
}
